# -*- coding:utf-8 -*-
'''
dblayer.py Created on Dec 13, stocklens01

@author: Nick Liang
'''
import sys
sys.path.append(sys.argv[0][:sys.argv[0].rfind('com/stocklens')])

import MySQLdb
import datetime
import decimal
import re
import time
import traceback
import json
from MySQLdb.cursors                       import DictCursor
from com.stocklens.stock.common.utils      import Logging
from datetime                              import timedelta

class DBAgent():
    '''
    DBAgent class is responsible for stock DB add/delete/modify/query operation.
    '''

    def __init__(self ,dbHost, dbPort, dbUser, dbPasswd, dbName):
        self.__dbHost   = dbHost
        self.__dbPort   = dbPort
        self.__dbUser   = dbUser
        self.__dbPasswd = dbPasswd
        self.__dbName   = dbName
        self.__conn     = MySQLdb.connect(host=dbHost,user=dbUser,passwd=dbPasswd,db=dbName,port=dbPort,cursorclass=DictCursor,charset='utf8')
        self.__curR     = self.__conn.cursor()
        self.__conn.ping(True)
        self.__startDate  = datetime.date(2005,1,1)
        self.__endDate    = datetime.date(2014,12,31)
        self.__bigPercent = 0.2
        self.__medPercent = 0.7
        self.__smlPercent = 1.0

    def close(self):
        try:
            self.__conn.close()
            self.__curR.close()
        except MySQLdb.Error,e:
            traceInfo = traceback.format_exc()
            Logging.LOGGER.error(traceInfo)

    def initCapitalRange(self):
        try:
            self.__conn.commit()
            queryCursor    = self.__conn.cursor()
            insertCursor   = self.__conn.cursor()
            oneDayDelta    = timedelta(days=1)
            count  = queryCursor.execute('select stockid, min(date) as min_date from stockdaily_bfq group by stockid order by stockid')
            print "count:%s" % count
            if count > 0 :
                stockList = queryCursor.fetchall()
                if stockList is None: return
                for s in stockList:
                    stockId   = s['stockid']
                    firstDate = s['min_date']
                    if firstDate < self.__startDate:
                        firstDate = self.__startDate
                    print "stockId:%s,firstDate:%s" % (stockId,firstDate)
                    iterDate = firstDate
                    values = list()
                    while iterDate <= self.__endDate:
                        value = (stockId, iterDate)
                        values.append(value)
                        iterDate = iterDate + oneDayDelta
                    if len(values) > 0:
                        insertCursor.executemany('INSERT INTO stock_capital_range(stock_id,date) VALUES(%s,%s)',values)
                        self.__conn.commit()
            self.__conn.commit()
            queryCursor.close()
            insertCursor.close()
        except MySQLdb.Error,e:
            traceInfo = traceback.format_exc()
            Logging.LOGGER.error(traceInfo)

    def initCapital(self, srcField, destField):
        try:
            self.__conn.commit()
            queryCursor    = self.__conn.cursor()
            updateCursor   = self.__conn.cursor()
            oneDayDelta    = timedelta(days=1)

            #querySql       = 'SELECT stockid, issue_date, %s FROM capitalstock WHERE stockid = "000001" AND %s IS NOT NULL ORDER BY stockid' % (srcField, srcField)
            querySql       = 'SELECT stockid, issue_date, %s FROM capitalstock WHERE %s IS NOT NULL ORDER BY stockid' % (srcField, srcField)
            updateSql      = 'UPDATE stock_capital_range SET %s' % destField
            updateSql      = updateSql + ' = %s WHERE stock_id = %s AND date >= %s AND date < %s'
            count  = queryCursor.execute(querySql)
            print "count:%s" % count
            if count > 0 :
                stockList = queryCursor.fetchall()
                if stockList is None: return
                updateValue   = 0.0
                iterStockId = ""
                startDate   = self.__startDate
                endDate     = self.__startDate
                for s in stockList:
                    stockId   = s['stockid']
                    iterDate  = s['issue_date']
                    srcValue  = s[srcField]
                    print "stockId:%s,iterDate:%s,%s:%s" % (stockId,iterDate,srcField,srcValue)
                    if iterStockId != stockId:
                        if iterStockId != "":
                            endDate = self.__endDate + oneDayDelta
                            values = (updateValue, iterStockId, startDate, endDate)
                            updateCursor.execute(updateSql,values)
                            self.__conn.commit()
                        iterStockId = stockId
                        startDate   = self.__startDate
                        endDate     = self.__startDate

                    endDate = iterDate
                    if endDate >= self.__startDate:
                        values = (updateValue, iterStockId, startDate, endDate)
                        updateCursor.execute(updateSql,values)
                        self.__conn.commit()
                    updateValue = srcValue
                    startDate = iterDate

                if iterStockId != "":
                    endDate = self.__endDate + oneDayDelta
                    values = (updateValue, iterStockId, startDate, endDate)
                    updateCursor.execute(updateSql,values)
                    self.__conn.commit()

            #querySql       = 'SELECT stock_id, MIN(date) AS min_date, %s FROM stock_capital_range WHERE %s != 0 AND stock_id = "000001" GROUP BY stock_id' % (srcField, srcField)
            querySql       = 'SELECT stock_id, MIN(date) AS min_date, %s FROM stock_capital_range WHERE %s != 0 GROUP BY stock_id' % (destField, destField)
            updateSql      = 'UPDATE stock_capital_range SET %s' % destField
            updateSql      = updateSql + ' = %s WHERE stock_id = %s AND date < %s'

            count  = queryCursor.execute(querySql)
            print "count:%s" % count
            if count > 0 :
                stockList = queryCursor.fetchall()
                if stockList is None: return
                updateValue   = 0.0
                iterStockId = ""
                startDate   = self.__startDate
                endDate     = self.__startDate
                for s in stockList:
                    stockId   = s['stock_id']
                    iterDate  = s['min_date']
                    srcValue  = s[destField]
                    print "stockId:%s,iterDate:%s,%s:%s" % (stockId,iterDate,destField,srcValue)
                    iterStockId = stockId
                    updateValue = srcValue
                    endDate     = iterDate
                    values = (updateValue, iterStockId, endDate)
                    updateCursor.execute(updateSql,values)
                    self.__conn.commit()

            self.__conn.commit()
            queryCursor.close()
            updateCursor.close()
        except MySQLdb.Error,e:
            traceInfo = traceback.format_exc()
            Logging.LOGGER.error(traceInfo)

    def calcTotalStock(self, destField):
        try:
            self.__conn.commit()
            queryCursor    = self.__conn.cursor()
            updateCursor   = self.__conn.cursor()

            #querySql       = 'SELECT date,COUNT(stock_id) AS total_count FROM stock_capital_range WHERE date = "2005-01-01" GROUP BY date ORDER BY date'
            querySql       = 'SELECT date,COUNT(stock_id) AS total_count FROM stock_capital_range GROUP BY date ORDER BY date'
            updateSql      = 'UPDATE stock_capital_range SET %s' % destField
            updateSql      = updateSql + ' = %s WHERE date = %s'
            count  = queryCursor.execute(querySql)
            print "count:%s" % count
            if count > 0 :
                stockList = queryCursor.fetchall()
                if stockList is None: return
                for s in stockList:
                    iterDate   = s['date']
                    totalCount = s['total_count']
                    print "date:%s,totalCount:%s,time:%s" % (iterDate,totalCount,time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
                    values = (totalCount, iterDate)
                    updateCursor.execute(updateSql,values)
                    self.__conn.commit()

            self.__conn.commit()
            queryCursor.close()
            updateCursor.close()
        except MySQLdb.Error,e:
            traceInfo = traceback.format_exc()
            Logging.LOGGER.error(traceInfo)

    def calcRange(self, srcField, destField, extraField = None):
        try:
            self.__conn.commit()
            queryCursor    = self.__conn.cursor()
            updateCursor   = self.__conn.cursor()
            oneDayDelta    = timedelta(days=1)
            currDate       = self.__startDate
            if srcField == 'tts':
                currDate       = datetime.date(2010,6,8)
            #self.__endDate = datetime.date(2014,12,31)
            while currDate <= self.__endDate:
                querySql       = 'SELECT date,stock_id,%s,total_stock FROM stock_capital_range WHERE date = "%s" ORDER BY %s DESC' % (srcField, currDate, srcField)
                #querySql       = 'SELECT date,stock_id,%s,total_stock FROM stock_capital_range ORDER BY date, %s DESC' % (srcField, srcField)
                if extraField is None:
                    updateSql = 'UPDATE stock_capital_range SET %s' % destField
                else:
                    updateSql = 'UPDATE stock_capital_range SET %s = %s, %s' % (destField, '%s', extraField)
                updateSql      = updateSql + ' = %s WHERE stock_id = %s AND date = %s'
                count  = queryCursor.execute(querySql)
                print "count:%s,currDate:%s,time:%s" % (count,currDate,time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
                if count > 0 :
                    stockList = queryCursor.fetchall()
                    if stockList is None: return
                    range    = 0
                    total    = 0
                    rangeAtt = 'UNKNOWN'
                    for s in stockList:
                        stockId    = s['stock_id']
                        totalStock = s['total_stock']
                        range = range + 1
                        total = totalStock

                        if extraField is None:
                            values = (range, stockId, currDate)
                        else:
                            if total > 0:
                                if float(range)/float(total) < self.__bigPercent:
                                    rangeAtt = 'BIG'
                                elif float(range)/float(total) < self.__medPercent:
                                    rangeAtt = 'MEDIUM'
                                else:
                                    rangeAtt = 'SMALL'
                            else:
                                rangeAtt = 'SMALL'
                            values = (range, rangeAtt, stockId, currDate)

                        updateCursor.execute(updateSql,values)
                        self.__conn.commit()
                currDate = currDate + oneDayDelta

            self.__conn.commit()
            queryCursor.close()
            updateCursor.close()
        except MySQLdb.Error,e:
            traceInfo = traceback.format_exc()
            Logging.LOGGER.error(traceInfo)

def db_test():
    import logging.config
    logfile = 'conf/stock.logging.cfg'
    logging.config.fileConfig(logfile)
    Logging.LOGGER = logging.getLogger('stock')
    stockdb = DBAgent('localhost',3306,'stocklens','stocklens','nickstock')
    '''
    print "time:%s,initCapitalRange" % time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    stockdb.initCapitalRange()
    print "time:%s,initCapital('TTS','tts')" % time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    stockdb.initCapital('TTS','tts')
    print "time:%s,initCapital('TAS','tas')" % time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    stockdb.initCapital('TAS','tas')
    print "time:%s,calcTotalStock('total_stock')" % time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    stockdb.calcTotalStock('total_stock')
    '''
    print "time:%s,calcRange('tts','tts_range')" % time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    stockdb.calcRange('tts','tts_range')

    print "time:%s,calcRange('tas','tas_range','tas_range_att')" % time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    stockdb.calcRange('tas','tas_range','tas_range_att')
    stockdb.close()
if __name__== '__main__':
    db_test()
